/**************************************************************************************************
	$Header: /pub/cvsroot/mydns/src/mydns/db.c,v 1.12 2002/09/14 03:26:40 bboy Exp $
	db.c: Functions for dealing with the database tables, etc.

	Copyright (C) 2002  Don Moore <bboy@bboy.net>

	This program is free software; you can redistribute it and/or modify
	it under the terms of the GNU General Public License as published by
	the Free Software Foundation; either version 2 of the License, or
	(at Your option) any later version.

	This program is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
	GNU General Public License for more details.

	You should have received a copy of the GNU General Public License
	along with this program; if not, write to the Free Software
	Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
**************************************************************************************************/

#include "named.h"


/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	DB_CONNECT
	Connect to the database.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
void
db_connect(void)
{
	u_char *host = conf_get(&Conf, "mysql-host"), *portp = NULL;
	u_char *database = conf_get(&Conf, "database");
	u_char *password = conf_get(&Conf, "mysql-password");
	u_char *user = conf_get(&Conf, "mysql-user");
	unsigned int port = 0;

	if (host && (portp = strchr(host, ':')))
	{
		port = atoi(portp + 1);
		*portp = '\0';
	}

	mysql = NULL;
	if (!(mysql = mysql_init(NULL)))
		Err(_("Unable to allocate MySQL data structure"));
	mysql_options(mysql, MYSQL_READ_DEFAULT_GROUP, "client");
	if (!(mysql_real_connect(mysql, host, user, password, database, port, NULL, 0)))
		ErrSQL(mysql, _("Error connecting to MySQL server at %s"), host);

	if (portp)
		*portp = ':';
}
/*--- db_connect() ------------------------------------------------------------------------------*/


/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	DB_OUTPUT_CREATE_TABLES
	Output SQL statements to create tables and exit.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
void
db_output_create_tables(void)
{
	load_config();

	/* Header */
	printf(
"-- 
--  Table layouts for "PACKAGE_STRING" ("PACKAGE_DATE")
--  "PACKAGE_COPYRIGHT"
-- 
--  You might create these tables with a command like:
-- 
--    $ %s --create-tables | mysql -hHOST -p -uUSER DATABASE
-- 
-- 
", progname);

	/* Zone/SOA table */
	printf("
-- 
--  Table structure for table '%s' (zones of authority)
-- 

CREATE TABLE IF NOT EXISTS %s (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  origin     CHAR(78) NOT NULL,
  ns         CHAR(255) NOT NULL,
  mbox       CHAR(255) NOT NULL,
  serial     INT UNSIGNED NOT NULL default '1',
  refresh    INT UNSIGNED NOT NULL default '%u',
  retry      INT UNSIGNED NOT NULL default '%u',
  expire     INT UNSIGNED NOT NULL default '%u',
  minimum    INT UNSIGNED NOT NULL default '%u',
  ttl        INT UNSIGNED NOT NULL default '%u',
  UNIQUE KEY (origin)
) TYPE=MyISAM;
\n",
	mydns_soa_table_name,
	mydns_soa_table_name,
	DNS_DEFAULT_REFRESH,
	DNS_DEFAULT_RETRY,
	DNS_DEFAULT_EXPIRE,
	DNS_DEFAULT_MINIMUM,
	DNS_DEFAULT_TTL
);

	/* Resource record table */
	printf("
-- 
--  Table structure for table '%s' (resource records)
-- 

CREATE TABLE IF NOT EXISTS %s (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  zone       INT UNSIGNED NOT NULL,
  name       CHAR(63) NOT NULL,
  type       ENUM('A','AAAA','CNAME','MX','NS','TXT'),
  data       CHAR(255) NOT NULL,
  aux        INT UNSIGNED NOT NULL,
  ttl        INT UNSIGNED NOT NULL default '%u',
  UNIQUE KEY rr (zone,name,type,data)
) TYPE=MyISAM;
\n",
	mydns_rr_table_name,
	mydns_rr_table_name,
	DNS_DEFAULT_TTL
);

	/* PTR table */
	printf("
-- 
--  Table structure for table '%s'
-- 

CREATE TABLE IF NOT EXISTS %s (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ip         INT UNSIGNED NOT NULL,
  name       CHAR(255) NOT NULL,
  ttl        INT UNSIGNED NOT NULL default '%u',
  UNIQUE KEY (ip)
) TYPE=MyISAM;
\n",
	mydns_ptr_table_name,
	mydns_ptr_table_name,
	DNS_DEFAULT_TTL
);
	exit(EXIT_SUCCESS);
}
/*--- db_output_create_tables() -----------------------------------------------------------------*/


/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	DB_SQL_NUMROWS
	Returns the number of rows from a specified result.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
static int
db_sql_numrows(const char *fmt, ...)
{
	va_list ap;
	char query[256];
	MYSQL_RES *res = NULL;
	int rv;

	va_start(ap, fmt);
	vsnprintf(query, sizeof(query), fmt, ap);
	va_end(ap);

	mysql->net.last_errno = 0;
	if (mysql_real_query(mysql, query, strlen(query)) || !(res = mysql_store_result(mysql)))
		ErrSQL(mysql, "%s", _("MySQL error"));
	rv = mysql_num_rows(res);
	mysql_free_result(res);
	return (rv);
}
/*--- db_sql_numrows() --------------------------------------------------------------------------*/


/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	DB_CHECK_COLUMN
	Makes sure the specified column name exists in the specified table for the database.
	Fatal if the column does not exist.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
static void
db_check_column(database, table, name)
	u_char *database;
	u_char *table;
	u_char *name;
{
	char *xdatabase, *xtable, *xname;

	SQLESC(database, xdatabase);
	SQLESC(table, xtable);
	SQLESC(name, xname);
	if (!db_sql_numrows("SHOW COLUMNS FROM %s.%s LIKE '%s'", xdatabase, xtable, xname))
	{
		Warnx(_("Required column `%s' in table `%s' (database `%s') not found or inaccessible"),
				name, table, database);
		if (mysql->net.last_errno)
			Warnx("%s: %s", _("MySQL said:"), mysql_error(mysql));
		else
		{
			Warnx(_("Do you need to create the tables in the `%s' database?"), database);
			Warnx(_("You can run `%s --create-tables' to output appropriate SQL commands"), progname);
		}
		exit(EXIT_FAILURE);
	}
}
/*--- db_check_column() -------------------------------------------------------------------------*/


/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	DB_VERIFY_TABLE
	Verifies each column in a comma-separated list.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
static void
db_verify_table(database, table, columns)
	u_char *database;
	u_char *table;
	u_char *columns;
{
	char fields[80], *f = fields, *name;
	char *xdatabase, *xtable;

	/* Check that the table itself exists */
	SQLESC(database, xdatabase);
	SQLESC(table, xtable);
	if (!db_sql_numrows("SHOW TABLES FROM %s LIKE '%s'", xdatabase, xtable))
	{
		Warnx(_("Required table `%s' in database `%s' not found or inaccessible"), table, database);
		if (mysql->net.last_errno)
			Warnx("%s: %s", _("MySQL said:"), mysql_error(mysql));
		else
		{
			Warnx(_("Do you need to create the tables in the `%s' database?"), database);
			Warnx(_("You can run `%s --create-tables' to output appropriate SQL commands"), progname);
		}
		exit(EXIT_FAILURE);
	}

	/* Check each field in field list */
	strncpy(fields, columns, sizeof(fields)-1);
	while ((name = strsep(&f, ",")))
		db_check_column(database, table, name);
}
/*--- db_verify_table() -------------------------------------------------------------------------*/


/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	DB_CHECK_OPTIONAL
	Check optional columns.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
void
db_check_optional(void)
{
	/* Optional columns in soa table */
	if (mydns_set_soa_use_active(mysql) > 0)
		Verbose(_("optional \"active\" column found in \"%s\" table"), mydns_soa_table_name);
	if (mydns_set_soa_use_xfer(mysql) > 0)
		Verbose(_("optional \"xfer\" column found in \"%s\" table"), mydns_soa_table_name);

	/* Optional columns in rr table */
	if (mydns_set_rr_use_active(mysql) > 0)
		Verbose(_("optional \"active\" column found in \"%s\" table"), mydns_rr_table_name);

	/* Optional columns in ptr table */
	if (mydns_set_ptr_use_active(mysql) > 0)
		Verbose(_("optional \"active\" column found in \"%s\" table"), mydns_ptr_table_name);
}
/*--- db_check_optional() -----------------------------------------------------------------------*/


/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
	DB_VERIFY_TABLES
	Makes sure the required tables exist and that we can read from them.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/
void
db_verify_tables(void)
{
	u_char *host = conf_get(&Conf, "mysql-host"), *portp = NULL;
	u_char *database = conf_get(&Conf, "database"), *xdatabase;
	u_char *password = conf_get(&Conf, "mysql-password");
	u_char *user = conf_get(&Conf, "mysql-user");
	unsigned int port = 0;

	if (host && (portp = strchr(host, ':')))
	{
		port = atoi(portp + 1);
		*portp = '\0';
	}

	if (!(mysql = mysql_init(NULL)))
		Err(_("Unable to allocate MySQL data structure"));
	if (!(mysql_real_connect(mysql, host, user, password, NULL, port, NULL, 0)))
		ErrSQL(mysql, _("Error connecting to MySQL server at %s"), host);

	if (portp)
		*portp = ':';

	/* Check existence of database */
	SQLESC(database, xdatabase);
	if (!db_sql_numrows("SHOW DATABASES LIKE '%s'", xdatabase))
	{
		Warnx(_("Required database `%s' not found or inaccessible"), database);
		if (mysql->net.last_errno)
			Warnx("%s: %s", _("MySQL said:"), mysql_error(mysql));
		else
			Warnx(_("Do you need to create the `%s' database?"), database);
		exit(EXIT_FAILURE);
	}
	if (mysql_select_db(mysql, database))
		ErrSQL(mysql, _("error selecting database"));

	db_verify_table(database, mydns_soa_table_name, MYDNS_SOA_FIELDS);
	db_verify_table(database, mydns_rr_table_name, MYDNS_RR_FIELDS);
	db_verify_table(database, mydns_ptr_table_name, MYDNS_PTR_FIELDS);

	db_check_optional();

	mysql_close(mysql);
	mysql = NULL;
}
/*--- db_verify_tables() ------------------------------------------------------------------------*/

/* vi:set ts=3: */
/* NEED_PO */
